package com.ch7.dal;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ch7.common.Conn;
import com.ch7.common.DataValidator;
import com.ch7.model.BlogInfo;

public class Blog {

	Conn conn=new Conn();
	
	public List<BlogInfo> getList(String keyword) throws SQLException{
		List<BlogInfo> list = new ArrayList<BlogInfo>();
		String sql ="select b.*,c.name as ClassName from blog b left join class c on b.classid=c.id";
		if(DataValidator.isNulloEmpty(keyword)){
			sql=sql+" order by id desc";
		}else{
			sql=sql+" where b.title like '%"+keyword+"%' order by id desc";
		}
		ResultSet rs = conn.executeQuery(sql);
		while(rs.next()){
			BlogInfo info = new BlogInfo();
			info.setId(rs.getInt("Id"));
			info.setTitle(rs.getString("Title"));
			info.setContext(rs.getString("Context"));
			info.setCreatedtime(rs.getDate("CreatedTime"));
			info.setClassid(rs.getInt("ClassId"));
			info.setClassName(rs.getString("ClassName"));
			list.add(info);
		}
		conn.close();
		return list;
	}
	
	public List<BlogInfo> getListByClassId(int classId) throws SQLException{
		List<BlogInfo> list = new ArrayList<BlogInfo>();
		String sql = "select b.*,c.name as ClassName from blog b left join class c on b.classid=c.id where b.classid="+classId+" order by id desc";
		System.out.println(sql);
		ResultSet rs = conn.executeQuery(sql);
		while(rs.next()){
			BlogInfo info = new BlogInfo();
			info.setId(rs.getInt("Id"));
			info.setTitle(rs.getString("Title"));
			info.setContext(rs.getString("Context"));
			info.setCreatedtime(rs.getDate("CreatedTime"));
			info.setClassid(rs.getInt("ClassId"));
			info.setClassName(rs.getString("ClassName"));
			list.add(info);
		}
		conn.close();
		return list;
	}
	
	public BlogInfo getBlogInfo(int id) throws SQLException{
		BlogInfo info = new BlogInfo();
		String sql="select b.*,c.name as ClassName from blog b left join class c on b.classid=c.id where b.id="+id+"";
		ResultSet rs = conn.executeQuery(sql);
		System.out.println(sql);
		if(rs.next()){
			info.setId(rs.getInt("Id"));
			info.setTitle(rs.getString("Title"));
			info.setContext(rs.getString("Context"));
			info.setCreatedtime(rs.getDate("CreatedTime"));
			info.setClassid(rs.getInt("ClassId"));
			info.setClassName(rs.getString("ClassName"));
			
		}
		conn.close();
		return info;
	}
	
	public int insert(BlogInfo info){
		String sql = "insert into blog(Title,Context,CreatedTime,ClassId) values ";
		sql=sql+"('"+info.getTitle()+"','"+info.getContext()+"',now(),"+info.getClassid()+")";
		int result=0;
		System.out.print(sql);
		result=conn.executeUpdate(sql);
		conn.close();
		return result;
	}
	
	public int update(BlogInfo info){
		String sql="update blog set"+" Title='"+info.getTitle()+"',Context='"+
	info.getContext()+"',ClassId='"+info.getClassid()+"'where id="+info.getId()+"";
		int result=0;
		System.out.println(sql);
		result=conn.executeUpdate(sql);
		conn.close();
		return result;
	}
	
	public int delete(int id){
		String sql="delete from blog where id ="+id+"";
		int result=0;
		result =conn.executeUpdate(sql);
		conn.close();
		return result;
	}
	
}
